CREATE OR REPLACE FUNCTION sp_project_type_insert(
	_name	character varying(255),
	_code	character varying(20) DEFAULT null,
	_description	character varying DEFAULT null,
	_display_index	integer DEFAULT null,
	_id_parent	integer DEFAULT null,
	_is_draft	boolean DEFAULT null,
	_creator	integer DEFAULT null
)RETURNS int AS $$
BEGIN
	INSERT INTO project_type(name,code,description,display_index,id_parent,is_draft,creator,updator,deletion_time)
	VALUES(_name,_code,_description,_display_index,_id_parent,_is_draft,_creator,_creator,null);
RETURN currval('project_type_id_seq'::regclass);
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_project_type_select(_id	integer)
RETURNS SETOF project_type
AS $$
BEGIN
	RETURN QUERY SELECT * FROM project_type WHERE id = _id;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_project_type_selectList (
	_limit	integer,
	_offset integer,
	_id	integer DEFAULT null,
	_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_description	character varying DEFAULT null,
	_display_index	integer DEFAULT null,
	_id_parent	integer DEFAULT null,
	_is_draft	boolean DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null
)
RETURNS SETOF project_type
AS $$
BEGIN
	IF _limit IS NULL THEN
		_limit = 2147483647;
	END IF;
	IF _offset IS NULL THEN
		_offset = 0;
	END IF;
RETURN QUERY SELECT * FROM project_type WHERE
	 	(_id is null OR id = _id)	
	AND (_name is null OR name = _name)	
	AND (_code is null OR code = _code)	
	AND (_description is null OR description = _description)	
	AND (_display_index is null OR display_index = _display_index)	
	AND (_id_parent is null OR id_parent = _id_parent)	
	AND (_is_draft is null OR is_draft = _is_draft)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo)	ORDER BY updated DESC  LIMIT _limit OFFSET _offset;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_project_type_count (
	_id	integer DEFAULT null,
	_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_description	character varying DEFAULT null,
	_display_index	integer DEFAULT null,
	_id_parent	integer DEFAULT null,
	_is_draft	boolean DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null
)
RETURNS int AS $$
BEGIN
	RETURN COUNT(*) FROM project_type WHERE
	 	(_id is null OR id = _id)	
	AND (_name is null OR name = _name)	
	AND (_code is null OR code = _code)	
	AND (_description is null OR description = _description)	
	AND (_display_index is null OR display_index = _display_index)	
	AND (_id_parent is null OR id_parent = _id_parent)	
	AND (_is_draft is null OR is_draft = _is_draft)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo);
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION sp_project_type_delete(_id integer)
	RETURNS INT as
$$
BEGIN
		DELETE FROM project_type
 		WHERE "id" = _id;
		RETURN 0;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_project_type_update 
(	_id integer,
	_name character varying(255),
	_code character varying(20),
	_description character varying,
	_display_index integer,
	_id_parent integer,
	_is_draft boolean,
	_updator integer,
	_deleted boolean,
	_deletion_time timestamp without time zone  ) 
RETURNS INT AS 
$$
BEGIN 
	UPDATE project_type 
	SET 		name = _name,
		code = _code,
		description = _description,
		display_index = _display_index,
		id_parent = _id_parent,
		is_draft = _is_draft,
		updated = now(),
		updator = _updator,
		deleted = _deleted,
		deletion_time = _deletion_time
 	WHERE id = _id;
	RETURN 0;
END; 
$$ LANGUAGE plpgsql;
